package Notifications;

import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;

import ImmovableIndia.ConnectionPool;
import InvestmentPojo.ArticlesDataPojo;
import InvestmentPojo.DefinitionsPojo;
import InvestmentPojo.EmailTempaltePojo;
import InvestmentPojo.FormsNCheckListsPojo;
import InvestmentPojo.MasterCircularsPojo;
import InvestmentPojo.OptionsList;
import InvestmentPojo.PostQueriesPojo;
import InvestmentPojo.PressNotesPojo;
import InvestmentPojo.circularsPojo;
import InvestmentPojo.contactusPojo;
import InvestmentPojo.faqPojo;
import InvestmentPojo.userInformationPojo;
import InvestmentPojo.userTablePojo;

public class notificationsDataLoading {
	
ConnectionPool pool=null;
Connection con=null;
PreparedStatement ps = null;
Statement st = null;
ResultSet rs = null;
public Connection getConnection() throws SQLException, Exception {
		pool=new ConnectionPool();
		return pool.getConnection();
}
	
public List getNotificationObject(String formid,String serviceId,String tableName)throws SQLException{
	
	
	List optionsList123 = null;
	try{
		con=getConnection();
		st=con.createStatement();
		rs=st.executeQuery("select * from "+tableName+" where serviceno="+serviceId+" order by Date_of_Notification desc");
		
		optionsList123=new ArrayList();
		while(rs.next()){
			 
			notificationTablePojo obj = new notificationTablePojo();
			obj.setNotificationNo(rs.getString(2));
			DateFormat df = new SimpleDateFormat("dd MMMM yyyy");
			
			String text = df.format(rs.getDate(3));
			obj.setDateofNotification(text);
			obj.setSubjectMatter(rs.getString(4));
			String text1 ="Different dates are provided";
			if(null != rs.getDate(5) && !"".equals(rs.getDate(5))){
				text1 = df.format(rs.getDate(5));
			}
			obj.setEffectiveDate(text1);
			obj.setType(rs.getString(10));
			
			
			System.out.println(text);
		/*	obj.setCreatedBy(rs.getString(5));
			obj.setCreatedOn(rs.getString(6));
			obj.setModifiedBy(rs.getString(7));
			obj.setModifiedOn(rs.getString(7));*/
			optionsList123.add(obj);
		}
		
		
		
	}catch(Exception e){
		e.printStackTrace();
	}finally{			
				try {
					if(null != rs)
						pool.releaseResources(rs);
					System.out.println("Connection RS closed........--------------");
					if(null != st)
						pool.releaseResources(st);
					System.out.println("Connection ST closed........--------------");
					if(null != con)
					pool.releaseConnection(con);
					System.out.println("Connection con closed........--------------");
				} catch (Exception e) {
					e.printStackTrace();
				}
	}
	
	return optionsList123;
	
}

public boolean setContactUsValues(contactusPojo cs)throws SQLException{

	boolean flag = false;
	try{
		con=getConnection();
		st=con.createStatement();
		
		
		ps = con.prepareStatement("INSERT INTO contactus VALUES(?,?,?,?)");
		ps.setString(1, (String)cs.getContactName());
		ps.setString(2, (String)cs.getContactEmail());
		ps.setString(3, (String)cs.getContactComments());
		ps.setString(4, (String)cs.getContactUserId());
			
		
		if(ps.executeUpdate() == 0)
			flag = false;
		else
			flag = true;
	}catch(Exception  e){
		e.printStackTrace();
	}finally{
		try {
			if(null != ps)
				pool.releaseResources(ps);
			System.out.println("Connection RS closed........--------------");
			if(null != st)
				pool.releaseResources(st);
			System.out.println("Connection ST closed........--------------");
			if(null != con)
			pool.releaseConnection(con);
			System.out.println("Connection con closed........--------------");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	return flag;
	

}
public List getCircularsObject(String serviceId,String tableName ,String method)throws SQLException{
	List li = null;
	try{

		con=getConnection();
		st=con.createStatement();
		if(tableName.equals("circulars")){
			tableName = "circulars";
		rs=st.executeQuery("select * from "+tableName+" where service_no="+serviceId+" order by Date_of_circular desc");
		
		li=new ArrayList();
		while(rs.next()){
			 
			circularsPojo obj = new circularsPojo();
			obj.setTopic(rs.getString(2));
			DateFormat df = new SimpleDateFormat("dd MMMM yyyy");
			
			String text = "";
			String text1 ="";
			if(null!=rs.getDate(7))
				text = df.format(null!=rs.getDate(7)?rs.getDate(7):"");
			if(null!=rs.getString(8))
			text1 = (null!=rs.getString(8)?rs.getString(8):"");
			String summeryOfCircular = rs.getString(3);
			
			obj.setCircular_subject(summeryOfCircular);
			obj.setType(text1);
			obj.setCreatedDate(text);
			System.out.println(text);
		
			li.add(obj);
		}
		}else{
			rs=st.executeQuery("select * from "+tableName+" where service_no="+serviceId+" order by Date desc");
			
			li=new ArrayList();
			while(rs.next()){
				 
				MasterCircularsPojo obj = new MasterCircularsPojo();
				obj.setTopic(rs.getString(2));
				DateFormat df = new SimpleDateFormat("dd MMMM yyyy");
				String text = "";
				if(null!=rs.getDate(3))
					text = df.format(null!=rs.getDate(3)?rs.getDate(3):"");
				obj.setIssueDate(text);
				obj.setExtra_column(rs.getString(5));
				li.add(obj);
			}
		}
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		try {
			if(null != rs)
				pool.releaseResources(rs);
			System.out.println("Connection RS closed........--------------");
			if(null != st)
				pool.releaseResources(st);
			System.out.println("Connection ST closed........--------------");
			if(null != con)
			pool.releaseConnection(con);
			System.out.println("Connection con closed........--------------");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	return li;
}


public List getArticlesObject(String serviceId ,String tableName,String formid)throws SQLException{
	List optionsList123 = null;
try{
	con=getConnection();
	st=con.createStatement();
	
	rs=st.executeQuery("select * from "+tableName+" where service_no="+serviceId);
	
	optionsList123=new ArrayList();
	while(rs.next()){
		 
		ArticlesDataPojo obj = new ArticlesDataPojo();
		obj.setTopic(rs.getString(2));
		DateFormat df = new SimpleDateFormat("dd MMMM yyyy");
		String text ="";
		if(null!= rs.getDate(6)){
			text = df.format(null!= rs.getDate(6)? rs.getDate(6):"");
		}
		obj.setDateofArticle(text);
		obj.setArticalSubjectMatter(rs.getString(3));
		obj.setCreatedBy(rs.getString(4));
		/*obj.setDateofNotification(text);
		
		String text1 = df.format(rs.getDate(5));
		obj.setEffectiveDate(text1);
		
		System.out.println(text);*/
		
		optionsList123.add(obj);
	}
	
	
	
}catch(Exception e){
	e.printStackTrace();
}finally{
	try {
		if(null != rs)
			pool.releaseResources(rs);
		System.out.println("Connection RS closed........--------------");
		if(null != st)
			pool.releaseResources(st);
		System.out.println("Connection ST closed........--------------");
		if(null != con)
		pool.releaseConnection(con);
		System.out.println("Connection con closed........--------------");
	} catch (Exception e) {
		e.printStackTrace();
	}
}

return optionsList123;

}


public boolean setUserInformation(userTablePojo utp)throws SQLException{

	boolean flag = false;
	try{
		con=getConnection();		
		ps = con.prepareStatement("INSERT INTO investment_user1 VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
		ps.setString(1, utp.getUserId().trim());
		ps.setString(2, (String)utp.getPwd().trim());
		ps.setString(3, (String)utp.getSurname().trim());
		ps.setString(4, (String)utp.getUsername().trim());
		
		DateFormat df = new SimpleDateFormat("dd MMMM yyyy");
		
		
		java.util.Date dt= new java.util.Date();
				
		//		df.parseObject(new String(utp.getDatepicker()));
		java.sql.Date sqlDate = new java.sql.Date(dt.getTime());
		
		String startDate = utp.getDatepicker();
		DateUtility utility = new DateUtility();
		
		ps.setDate(5, utility.getUserSqlDate(utp.getDatepicker()));
		ps.setString(6, (String)utp.getEmail());
		
		ps.setString(7, utp.getTel().trim());
		ps.setString(8,utp.getUserAddress().trim());
		ps.setString(9,utp.getPtc());
		ps.setString(10,utp.getUsername().trim());
		
		int isAdmin = 0;
		if(utp.isAdmin())
			isAdmin = 1;
		ps.setInt(11, isAdmin);
		ps.setString(13, utp.getUsername());
		ps.setString(12,utp.getUserType());
		ps.setDate(14,sqlDate);
		ps.setString(15,"YES");
		if(ps.executeUpdate() == 0)
			flag = false;
		else
			flag = true;
		//ps.close();
	}catch(ClassNotFoundException  ce){
		ce.printStackTrace();
	}catch(SQLException  se){
		se.printStackTrace();
	}catch(Exception e){
		e.printStackTrace();
		
	}finally{
		try {
			if(null != ps)
				pool.releaseResources(ps);
			System.out.println("Connection RS closed........--------------");
			if(null != con)
			pool.releaseConnection(con);
			System.out.println("Connection con closed........--------------");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return flag;
	}
	
	

}
/*
 * 
 * 
 * @ Returns the value of user id is authenticated or not.
 */
public boolean isAuthenticated(String userName , String password,boolean admin)throws SQLException{
	boolean flag = false;
	List optionsList123 = null;
	try{
		con=getConnection();	
		
		if(admin){
			rs =con.createStatement().executeQuery("select user_password from investment_user1  where user_Id='"+userName+"' and admin_user_flag= 1");
		}else{
		    rs=con.createStatement().executeQuery("select user_password from investment_user1  where user_Id='"+userName+"' and isUserAccess = 'YES'");	
		}
		while(rs.next()){
			 
			String password1 = rs.getString(1);
			if(password1.equals(password))
				flag = true;
		}
		
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		try {
			if(null != rs)
				pool.releaseResources(rs);
			System.out.println("Connection RS closed........--------------");
			if(null != con)
			pool.releaseConnection(con);
			System.out.println("Connection con closed........--------------");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	return flag;
}

public List getFAQDataList(String serviceId,String tableName ,String method)throws SQLException{
	List li = null;
	try{

		con=getConnection();	
		st=con.createStatement();
		
		rs=st.executeQuery("select * from "+tableName+" where service_no="+serviceId);
		
		li=new ArrayList();
		while(rs.next()){
			 
			faqPojo obj = new faqPojo();
			//obj.setSeqNo(Integer.parseInt(rs.getString(1)));
			obj.setSeqNo(rs.getInt(1));
			obj.setQuestion(rs.getString(2));
			obj.setAnswer(rs.getString(3));
			/*DateFormat df = new SimpleDateFormat("dd MMMM yyyy");
			
			String text = df.format(rs.getDate(3));
			
			System.out.println(text);*/
		
			li.add(obj);
		}
		
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		try {
			if(null != rs)
				pool.releaseResources(ps);
			System.out.println("Connection RS closed........--------------");
			if(null != st)
				pool.releaseResources(st);
			System.out.println("Connection ST closed........--------------");
			if(null != con)
			pool.releaseConnection(con);
			System.out.println("Connection con closed........--------------");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	
	
	return li;
}
public List getPressNotesDataLoading(String serviceId,String tableName ,String method)throws SQLException{
	List li = null;
	try{

		con=getConnection();	
		st =con.createStatement();
		
		rs=st.executeQuery("select * from "+tableName+" where ServiceNum="+serviceId);
		
		
		/*(`ServiceNum`,`Slumber`,`Subject`,`Notes`,`CreatedBy`,`CreatedOn`)*/
		li=new ArrayList();
		while(rs.next()){
			 
			PressNotesPojo obj = new PressNotesPojo();
			obj.setSeqNo(Integer.parseInt(rs.getString(1)));
			obj.setNnotes(rs.getString(3));
			obj.setSubject(rs.getString(2));
			DateFormat df = new SimpleDateFormat("dd MMMM yyyy");
			String text ="";
			if(null!= rs.getDate(6)){
				text = df.format(null!= rs.getDate(6)? rs.getDate(6):"");
			}
			obj.setNormal_date(text);
			li.add(obj);
		}
		
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		try {
			if(null != rs)
				pool.releaseResources(rs);
			System.out.println("Connection RS closed........--------------");
			if(null != st)
				pool.releaseResources(st);
			System.out.println("Connection ST closed........--------------");
			if(null != con)
			pool.releaseConnection(con);
			System.out.println("Connection con closed........--------------");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	
	
	return li;
}

public List getDefinitionsDataLoading(String serviceId,String tableName ,String method) throws SQLException {
	List li = null;
	try{
		con=getConnection();
		st=con.createStatement();
		String definitionName ="";
		
		rs=st.executeQuery("select * from "+tableName+" where Service_No="+serviceId+" order by Section_Rule asc");
		
		
		/*(`ServiceNum`,`Slumber`,`Subject`,`Notes`,`CreatedBy`,`CreatedOn`)*/
		li=new ArrayList();
		while(rs.next()){
			 
			DefinitionsPojo obj = new DefinitionsPojo();
			obj.setService_No(rs.getInt(1));
			obj.setSection_Rule(rs.getString(2));
			obj.setAct_rule(rs.getString(3));
			obj.setCaption(rs.getString(4));
			obj.setDefinition(rs.getString(5));
			obj.setCreated_by(rs.getString(6));
			obj.setCreated_by(rs.getString(7));
			obj.setDefinitionName(definitionName);
			//obj.setCreated_on(rs.getString(6));
			
			/*DateFormat df = new SimpleDateFormat("dd MMMM yyyy");
			
			String text = df.format(rs.getDate(3));
			
			System.out.println(text);*/
		
			li.add(obj);
		}
		
		
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		try {
			if(null != rs)
				pool.releaseResources(rs);
			System.out.println("Connection RS closed........--------------");
			if(null != st)
				pool.releaseResources(st);
			System.out.println("Connection ST closed........--------------");
			if(null != con)
			pool.releaseConnection(con);
			System.out.println("Connection con closed........--------------");
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
	
	
	
	return li;
}

public List getFormsNCheckListsDataLoading(String serviceId,String tableName ,String method) throws SQLException {
	List li = null;
	try{
		con=getConnection();
		st=con.createStatement();
		
		rs=st.executeQuery("select * from "+tableName+" where Service_No="+serviceId);
		
		li=new ArrayList();
		while(rs.next()){
			 
			FormsNCheckListsPojo obj = new FormsNCheckListsPojo();
			obj.setServiceNo(rs.getInt(1));
			obj.setSection_rule(rs.getString(2));
			obj.setSummary(rs.getString(3));
			obj.setCreatedBy(rs.getString(4));
			obj.setCreatedOn(rs.getString(5));
			//obj.setCreated_on(rs.getString(6));
			
			/*DateFormat df = new SimpleDateFormat("dd MMMM yyyy");
			
			String text = df.format(rs.getDate(3));
			
			System.out.println(text);*/
		
			li.add(obj);
		}
		
		
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		try {
			if(null != rs)
				pool.releaseResources(rs);
			System.out.println("Connection RS closed........--------------");
			if(null != st)
				pool.releaseResources(st);
			System.out.println("Connection ST closed........--------------");
			if(null != con)
			pool.releaseConnection(con);
			System.out.println("Connection con closed........--------------");
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
	
	
	
	return li;
}

/**
 * @param textlable
 * @param startNumber
 * @param endNumber
 * @param userId
 * @param selectedPostQueries
 * @return
 * @throws SQLException
 */
public  boolean updatePostYourQueiries(HttpServletRequest request ,String textlable, int startNumber,int endNumber,String userId, String selectedPostQueries,String IIIS)throws SQLException{
		
		boolean flag = false;
	  try{
		  int isUpdate = 0;
		  con=getConnection();
		  st=con.createStatement(); 

	  rs =con.createStatement().executeQuery("select max(queire_id) from post_queiries_mstr");
	  int maxQueryId = 0;
			 
	  while(rs.next()) {
		  maxQueryId = rs.getInt(1);
			}
		if(maxQueryId == 0)	{
			maxQueryId = 1;
		}else{
			maxQueryId = maxQueryId + 1;
		}
	  
	  isUpdate =st.executeUpdate("insert  into `post_queiries_mstr`(`userId`,`post_List_Name`,`queire_id`) "+
				" values" + 
				" ('"+userId+"','"+selectedPostQueries+"',"+maxQueryId+")");
	  
	  if(isUpdate == 1){
		  for(int i=startNumber;i<=endNumber;i++){
			  String temp = "text"+i;
			  String text1 = null != request.getParameter(temp)?request.getParameter(temp):""; 
			  st.addBatch("insert  into `post_queiries_child`(`quire_id`,`queire_column_id`,`query_text_value`,`qury_date_of_post`,`createdBy`,`updateBy`) "+
						" values" + 
						" ("+maxQueryId+",'"+textlable+i+"','"+text1+"',DATE_FORMAT(NOW(), '%d/%m/%Y'),'"+userId+"','"+userId+"')");
			  
		  }
		  st.executeBatch();
	  
	  }
	  request.setAttribute("isUpdated", isUpdate);
	  
	  if(isUpdate == 1){
		  flag = true;
	  }
	  }catch(Exception e){
		  e.printStackTrace();
		  if(con!=null)
		  con.rollback();
	  }finally{
		  try {
				if(null != rs)
					pool.releaseResources(rs);
				System.out.println("Connection RS closed........--------------");
				if(null != st)
					pool.releaseResources(st);
				System.out.println("Connection ST closed........--------------");
				if(null != con)
				pool.releaseConnection(con);
				System.out.println("Connection con closed........--------------");
			} catch (Exception e) {
				e.printStackTrace();
			}
		  return flag;
	  }
}
public List getAdminPostQueyData(String selectedPostId,String tableName ,String method)throws SQLException{
	List li = null;
	try{

		
		con=getConnection();	
		st =con.createStatement();
		
		rs=st.executeQuery("select quire_id,queire_column_id,query_text_value, userId,user_surName,user_middle_name,User_email,qury_date_of_post from  post_queiries_child pqc,post_queiries_mstr pqm,investment_user1 iu where quire_id in (select queire_id from post_queiries_mstr where  post_list_name = '"+selectedPostId+"' ) and pqc.quire_id = pqm.queire_id and userId = iu.user_id order by iu.user_name");
		
		
		/*(`ServiceNum`,`Slumber`,`Subject`,`Notes`,`CreatedBy`,`CreatedOn`)*/
		li=new ArrayList();
		while(rs.next()){
			 
			PostQueriesPojo obj = new PostQueriesPojo();
			obj.setQueryId(rs.getString(1));
			obj.setQueryColumnId(rs.getString(2));
			obj.setQueryTextValue(rs.getString(3));
			obj.setSelectedText(rs.getString(4));
			obj.setUserId(rs.getString(4));
			obj.setUserMailId(rs.getString(7));
			obj.setQueryPostedDate(rs.getString(8));
			
			li.add(obj);
		}
		//select * from investment_user1
		
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		try {
			if(null != rs)
				pool.releaseResources(rs);
			System.out.println("Connection RS closed........--------------");
			if(null != st)
				pool.releaseResources(st);
			System.out.println("Connection ST closed........--------------");
			if(null != con)
			pool.releaseConnection(con);
			System.out.println("Connection con closed........--------------");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	return li;
}




public List getAdminUserDetailsData(String userName, String fromDate, String toDate)throws SQLException{
	List li = null;
	try{

		
		con=getConnection();	
		st =con.createStatement();
		
		rs=st.executeQuery(" select user_name, user_id ,user_middle_name,user_email,user_type,created_on,admin_user_flag,user_telephone from investment_user1 order by created_on");
		li=new ArrayList();
		while(rs.next()){
			 
			userInformationPojo obj = new userInformationPojo();
			obj.setUserId(rs.getString(2));
			obj.setUserFirstName(rs.getString(1));
			obj.setUserMiddleName(rs.getString(3));
			obj.setUserEmailId(rs.getString(4));
			obj.setUserTypel(rs.getString(5));
			obj.setCreatedOn(rs.getString(6));
			obj.setAdminFlag(rs.getString(7));
			obj.setUserTelephone(rs.getString(8));
			
			li.add(obj);
		}
		
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		try {
			if(null != rs)
				pool.releaseResources(rs);
			System.out.println("Connection RS closed........--------------");
			if(null != st)
				pool.releaseResources(st);
			System.out.println("Connection ST closed........--------------");
			if(null != con)
			pool.releaseConnection(con);
			System.out.println("Connection con closed........--------------");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	return li;
}
public String getWebsiteContent(String formid,String serviceId,String tableName)throws SQLException{
	
	String webContent = "";
	try{
		con=getConnection();
		st=con.createStatement();
		rs=st.executeQuery("select * from "+serviceId+" where webId="+tableName);
		
		while(rs.next()){
			 
		Blob blob = rs.getBlob(2);
		byte[] bdata = blob.getBytes(1, (int) blob.length());
		webContent = new String(bdata);
			
			
			
			
		}
		
		
		
	}catch(Exception e){
		e.printStackTrace();
	}finally{			
				try {
					if(null != rs)
						pool.releaseResources(rs);
					System.out.println("Connection RS closed........--------------");
					if(null != st)
						pool.releaseResources(st);
					System.out.println("Connection ST closed........--------------");
					if(null != con)
					pool.releaseConnection(con);
					System.out.println("Connection con closed........--------------");
				} catch (Exception e) {
					e.printStackTrace();
				}
	}
	
	return webContent;
	
}

public String getWebsiteTitles(String tableName,String serviceNo, String columnName){

	String definitionName ="";
	try{
		con=getConnection();
		st=con.createStatement();
		
		
			rs = st.executeQuery("select distinct("+columnName+")  from "+tableName+" where Service_No="+serviceNo);
			while(rs.next()){
				definitionName = definitionName +"~~"+ rs.getString(1);
				
			}
			
	}catch(Exception e){
		e.printStackTrace();
	}finally{
	try {
			if(null != rs)
				pool.releaseResources(rs);
			System.out.println("Connection RS closed........--------------");
			if(null != st)
				pool.releaseResources(st);
			System.out.println("Connection ST closed........--------------");
			if(null != con)
			pool.releaseConnection(con);
			System.out.println("Connection con closed........--------------");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
return definitionName;

}
public String getNotificationTitles(String tableName,String serviceNo, String columnName){

	String notifcationTypes ="";
	try{
		con=getConnection();
		st=con.createStatement();
		
		
			rs = st.executeQuery("select distinct("+columnName+")  from "+tableName+" where ServiceNo="+serviceNo);
			while(rs.next()){
				notifcationTypes = notifcationTypes +"~~"+ rs.getString(1);
				
			}
			
	}catch(Exception e){
		e.printStackTrace();
	}finally{
	try {
			if(null != rs)
				pool.releaseResources(rs);
			System.out.println("Connection RS closed........--------------");
			if(null != st)
				pool.releaseResources(st);
			System.out.println("Connection ST closed........--------------");
			if(null != con)
			pool.releaseConnection(con);
			System.out.println("Connection con closed........--------------");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
return notifcationTypes;

}
public boolean isUserExist(String userId, String columnName){

	boolean flag =false;
	try{
		con=getConnection();
		st=con.createStatement();
		
			rs = st.executeQuery("select * from investment_user1 where user_id='"+userId+"'");
			while(rs.next()){
				flag = true;	
				
			}
			
			
	}catch(Exception e){
		e.printStackTrace();
	}finally{
	try {
			if(null != rs)
				pool.releaseResources(rs);
			System.out.println("isUserExist Connection RS closed........--------------");
			if(null != st)
				pool.releaseResources(st);
			System.out.println("isUserExist Connection ST closed........--------------");
			if(null != con)
			pool.releaseConnection(con);
			System.out.println("isUserExist Connection con closed........--------------");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	return flag;

}
public List getUserData(String userId, String tableName)throws SQLException{
List li = null;
try{
	con=getConnection();	
	st =con.createStatement();
	rs=st.executeQuery(" select * from "+tableName+" where  user_id='"+userId+"'");
	li=new ArrayList();
	if(rs.next()){
		userInformationPojo obj = new userInformationPojo();
		
		obj.setUserId(rs.getString(1));
		obj.setUserFirstName(rs.getString(4));
		obj.setUserMiddleName(rs.getString(3));
		obj.setUserEmailId(rs.getString(6));
		obj.setUserTypel(rs.getString(12));
		obj.setCreatedOn(rs.getString(6));
		obj.setAdminFlag(rs.getString(11));
		obj.setUserFirstName(rs.getString(10));
		li.add(obj);
	}
}catch(Exception e){
	e.printStackTrace();
}finally{
	try {
		if(null != rs)
			pool.releaseResources(rs);
		System.out.println("Connection RS closed........--------------");
		if(null != st)
			pool.releaseResources(st);
		System.out.println("Connection ST closed........--------------");
		if(null != con)
		pool.releaseConnection(con);
		System.out.println("Connection con closed........--------------");
	} catch (Exception e) {
		e.printStackTrace();
	}
}
return li;
}
public boolean updatePassword(String tableName, String column_for_update, String valueForUpdate, String clauses1 ,String clauses2,String clause3 )throws SQLException{
	boolean flag = false;
	try{
		con=getConnection();
		st=con.createStatement();
		StringBuffer sb= new StringBuffer();
		sb.append("update "+tableName+" set "+column_for_update+" = '"+valueForUpdate+"'");
		
		if(!"".equals(clauses1))
		sb.append("where "+clauses1);
		
		if(!"".equals(clauses2))
				sb.append(" and "+clauses2);
		if(!"".equals(clause3))
			sb.append("and "+clause3);
		ps = con.prepareStatement(sb.toString());
		if(ps.executeUpdate() == 0)
			flag = false;
		else
			flag = true;
	}catch(Exception  e){
		e.printStackTrace();
	}finally{
		try {
			if(null != ps)
				pool.releaseResources(ps);
			System.out.println("Connection RS closed........--------------");
			if(null != st)
				pool.releaseResources(st);
			System.out.println("Connection ST closed........--------------");
			if(null != con)
			pool.releaseConnection(con);
			System.out.println("Connection con closed........--------------");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	return flag;
}
public boolean updateUserPassword(String tableName, String column_for_update, String valueForUpdate, String clauses1 ,String clauses2,String clause3 )throws SQLException{
	boolean flag = false;
	try{
		con=getConnection();
		st=con.createStatement();
		StringBuffer sb= new StringBuffer();
			sb.append("update "+tableName+" set "+column_for_update+" = '"+valueForUpdate+"'");
		if(null != clauses1 && !"".equals(clauses1))
			sb.append("where "+clauses1);
		if(null != clauses1 && !"".equals(clauses2))
			sb.append("and "+clauses2);
		if(null != clauses1 && !"".equals(clause3))
			sb.append("and "+clause3);
	
		ps = con.prepareStatement(sb.toString());
		if(ps.executeUpdate() == 0)
			flag = false;
		else
			flag = true;
	}catch(Exception  e){
		e.printStackTrace();
	}finally{
		try {
			if(null != ps)
				pool.releaseResources(ps);
			System.out.println("Connection RS closed........--------------");
			if(null != st)
				pool.releaseResources(st);
			System.out.println("Connection ST closed........--------------");
			if(null != con)
			pool.releaseConnection(con);
			System.out.println("Connection con closed........--------------");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	return flag;
}

public List getTemplateContent(String tableName,  String clauses1 ,String clauses2,String clause3 )throws SQLException{
	List li = null;
	try{
		con=getConnection();
		st=con.createStatement();
		StringBuffer sb= new StringBuffer();
			sb.append("select * from "+tableName);
		if(null != clauses1 && !"".equals(clauses1))
			sb.append(" where "+clauses1);
		if(null != clauses1 && !"".equals(clauses2))
			sb.append("and "+clauses2);
		if(null != clauses1 && !"".equals(clause3))
			sb.append("and "+clause3);
	
		rs=st.executeQuery(sb.toString());
		
		li=new ArrayList();
		if(rs.next()){
			EmailTempaltePojo obj = new EmailTempaltePojo();
			obj.setSlno(rs.getInt(1));
			obj.setTemplateId(rs.getInt(2));
			Blob blob = rs.getBlob(4);
			byte[] bdata = blob.getBytes(1, (int) blob.length());
			String webContent = new String(bdata);
			
			obj.setTemplateText(webContent);
			obj.setCreatedBy(rs.getString(4));
			obj.setCreatedOn(rs.getString(5));
			obj.setModifiedBy(rs.getString(6));
			obj.setModifiedOn(rs.getString(7));
			obj.setSubject(rs.getString(9));
				li.add(obj);
		}	
		
		
	}catch(Exception  e){
		e.printStackTrace();
	}finally{
		try {
			if(null != ps)
				pool.releaseResources(ps);
			System.out.println("Email Templates Connection RS closed........--------------");
			if(null != st)
				pool.releaseResources(st);
			System.out.println("Email Templates Connection ST closed........--------------");
			if(null != con)
			pool.releaseConnection(con);
			System.out.println("Email Connection con closed........--------------");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	return li;
}
public List getDropDownList(String tableName,  String serviceId)throws SQLException{
	List li = null;
	Connection con=null;
	Statement st=null;
	ResultSet rs=null;
	String optionvalue="";
	try{
		con=getConnection();
		st=con.createStatement();
		rs=st.executeQuery("select * from investment_in_india where form_id = "+serviceId+" order by option_label");
		li = new ArrayList();
		while(rs.next()){
			OptionsList obj = new OptionsList();
			obj.setOptionId(rs.getString(1));
			obj.setListTypeId(rs.getString(2));
			obj.setOptionLabel(rs.getString(3));
			obj.setOptionValue(rs.getString(4));
			obj.setQestFlag(rs.getString(7));
			obj.setShowFlag(rs.getString(8));
			li.add(obj);
		}
	}catch(Exception e ){
		e.printStackTrace();
	}finally{
		try {
			if(null != rs)
				pool.releaseResources(rs);
			System.out.println("Connection RS closed........--------------");
			if(null != st)
				pool.releaseResources(st);
			System.out.println("Connection ST closed........--------------");
			if(null != con)
			pool.releaseConnection(con);
			System.out.println("Connection con closed........--------------");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	return li;
}

}